package com.feib.stms.batch.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import org.apache.commons.lang.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.stereotype.Repository;

import com.feib.stms.batch.domain.BusinessDay;
import com.feib.stms.batch.domain.DeliveryPaymentChkSum;
import com.feib.stms.batch.domain.UploadFile;
import com.feib.stms.batch.dao.rowmapper.BusinessDayRowMapper;
import com.feib.stms.batch.dao.rowmapper.DeliveryPaymentChkSumRowMapper;
import com.feib.stms.batch.dao.rowmapper.UploadFileRowMapper;



@Repository("deliveryPaymentChkMQSumJdbcDao") 
public class DeliveryPaymentChkSumMQJdbcDao extends BaseJdbcDao {



	/**
	 * 取得待驗資資料
	 * @return
	 */
	public List<UploadFile> queryToDoChkUploadFile(Date nextBussinessDay, boolean firstTime)
	{
		StringBuffer sql = new StringBuffer();
		List<Object> args = new ArrayList<Object>();

		sql.append("SELECT UPLOAD_FILE_PK ");
		sql.append(", FILE_FORMAT ");
		sql.append(", FILE_STATUS ");
		sql.append(", DELIVERY_DATE");
		sql.append(" from UPLOAD_FILE "); 
		sql.append(" where DELIVERY_DATE >= trunc( ? ) ");

		args.add(nextBussinessDay);

		sql.append(" AND FILE_FORMAT =1 ");

		if (firstTime)
		{
			sql.append(" AND FILE_STATUS = 0");

		}
		else
		{
			sql.append(" AND FILE_STATUS < 5");

		}

		logger.debug("SQL: {}", sql.toString());
		logger.debug("Args: {}", args.toString());


		return this.getJdbcTemplate().query(sql.toString(), new UploadFileRowMapper(),args.toArray()); 
	}


	public Date getNextBusinessDay(Date day){
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();

		sql.append("SELECT min(THE_DATE) as THE_DATE");
		sql.append(" FROM BUSINESS_DAY WHERE THE_DATE > :THE_DATE AND STATUS = 'W'");

		parameters.put("THE_DATE", day);

		logger.debug("SQL: {}", sql.toString());
		logger.debug("Args: {}", parameters);
		
		BusinessDay b=new BusinessDay();

		try{	
			 b = DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new BusinessDayRowMapper(), parameters));
		}catch (EmptyResultDataAccessException e) {
			 b = null;
		}
		return (null != b ? b.getTheDate() : null);	

	}


	/**
	 * 檔案明細中，驗資尚未通過的總筆數
	 * @param uploadFilePk 驗資檔PK
	 * @return
	 */
	public int countAllToDoRecords(Long uploadFilePk) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		Map<String, Object> parameters = new HashMap<String, Object>();

		sql.append("SELECT COUNT(*) FROM DELIVERY_CHK_SUM  D WHERE D.UPLOAD_FILE_PK= :UPLOAD_FILE_PK");

		parameters.put("UPLOAD_FILE_PK",  uploadFilePk);

		logger.debug("SQL: {}", sql.toString());
		logger.debug("Args: {}", parameters);

		int updateCount = this.getSimpleJdbcTemplate().queryForInt(sql.toString(), parameters);

		return updateCount;

	}


	/**
	 * 取得待處理的明細
	 * @param filePk 驗資檔案PK
	 * @param begIdx 待處理紀錄 起始
	 * @param endIdx 待處理紀錄 結束
	 * @return
	 */
	public List<DeliveryPaymentChkSum> getToDoRecords(Long uploadFilePk, int begIdx,int endIdx) {

		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append(" SELECT * FROM (");
		sql.append(" SELECT * FROM DELIVERY_CHK_SUM  D ");
		sql.append(" WHERE D.UPLOAD_FILE_PK= :UPLOAD_FILE_PK");
		sql.append(" )");
		sql.append(" WHERE  ROWNUM >= :START");
		sql.append(" AND    ROWNUM <= :END");

		parameters.put("UPLOAD_FILE_PK",  uploadFilePk);
		parameters.put("START",  begIdx);
		parameters.put("END",  endIdx);

		logger.debug("SQL: {}", sql.toString());
		logger.debug("Args: {}", parameters);

		return this.getSimpleJdbcTemplate().query(sql.toString(), new DeliveryPaymentChkSumRowMapper(),parameters); 

	}


	public int updateUploadFileFileStatus(UploadFile uploadFile){
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();

		sql.append("UPDATE UPLOAD_FILE SET FILE_STATUS = :FILE_STATUS ,UPDATED_DATE = :UPDATED_DATE, UPDATED_BY= :UPDATED_BY ");

		parameters.put("FILE_STATUS",  uploadFile.getFileStatus());
		parameters.put("UPDATED_DATE",  uploadFile.getUpdatedDate());
		parameters.put("UPDATED_BY",  uploadFile.getUpdatedBy());


		sql.append(" WHERE UPLOAD_FILE_PK = :UPLOAD_FILE_PK");
		parameters.put("UPLOAD_FILE_PK",  uploadFile.getUploadFilePk());


		logger.debug("SQL: {}", sql.toString());
		logger.debug("Args: {}", parameters);
		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}


	public int updateDeliveryPaymentChkSum(DeliveryPaymentChkSum deliveryPaymentChkSum) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append(" UPDATE  DELIVERY_CHK_SUM ");
		sql.append("  SET LESS_AMOUNT = :LESS_AMOUNT");
		sql.append(", DEAL_RESULT_CODE = :DEAL_RESULT_CODE");
		sql.append(",  UPDATED_BY = :UPDATED_BY");
		sql.append(",  UPDATED_DATE = :UPDATED_DATE");
		sql.append(",  ACCOUNT_BALANCE = :ACCOUNT_BALANCE");

		if(StringUtils.isNotEmpty(deliveryPaymentChkSum.getRtChkMsgId1())){
			sql.append(", RT_CHK_MSG_ID_1 = :RT_CHK_MSG_ID_1");
			sql.append(", RT_CHK_OUT_DATE_1 = :RT_CHK_OUT_DATE_1");
			sql.append(", RT_CHK_IN_DATE_1 = :RT_CHK_IN_DATE_1");
			sql.append(", RT_CHK_RESULT_1 = :RT_CHK_RESULT_1");

			parameters.put("RT_CHK_MSG_ID_1", deliveryPaymentChkSum.getRtChkMsgId1());
			parameters.put("RT_CHK_OUT_DATE_1", deliveryPaymentChkSum.getRtChkOutDate1());
			parameters.put("RT_CHK_IN_DATE_1", deliveryPaymentChkSum.getRtChkInDate1());
			parameters.put("RT_CHK_RESULT_1", deliveryPaymentChkSum.getRtChkResult1());
		}

		if(StringUtils.isNotEmpty(deliveryPaymentChkSum.getRtChkMsgId2())){
			sql.append(", RT_CHK_MSG_ID_2 = :RT_CHK_MSG_ID_2");
			sql.append(", RT_CHK_OUT_DATE_2 = :RT_CHK_OUT_DATE_2");
			sql.append(", RT_CHK_IN_DATE_2 = :RT_CHK_IN_DATE_2");
			sql.append(", RT_CHK_RESULT_2 = :RT_CHK_RESULT_2");

			parameters.put("RT_CHK_MSG_ID_2", deliveryPaymentChkSum.getRtChkMsgId2());
			parameters.put("RT_CHK_OUT_DATE_2", deliveryPaymentChkSum.getRtChkOutDate2());
			parameters.put("RT_CHK_IN_DATE_2", deliveryPaymentChkSum.getRtChkInDate2());
			parameters.put("RT_CHK_RESULT_2", deliveryPaymentChkSum.getRtChkResult2());
		}


		sql.append(" WHERE  DELIVERY_CHK_SUM_PK = :DELIVERY_CHK_SUM_PK");

		parameters.put("LESS_AMOUNT", deliveryPaymentChkSum.getLessAmount());
		parameters.put("DEAL_RESULT_CODE", deliveryPaymentChkSum.getDealResultCode());
		parameters.put("UPDATED_BY", deliveryPaymentChkSum.getUpdatedBy());
		parameters.put("UPDATED_DATE", deliveryPaymentChkSum.getUpdatedDate());
		parameters.put("ACCOUNT_BALANCE", deliveryPaymentChkSum.getAccountBalance());		
		parameters.put("DELIVERY_CHK_SUM_PK", deliveryPaymentChkSum.getDeliveryPaymentChkSumPk());
		
		

		logger.debug("SQL: {}", sql.toString());
		logger.debug("Args: {}", parameters);

		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}


}
